﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Diagnostics;
using System.Data.Common;
using System.Xml.Linq;
using Wintellect.PowerCollections;
using System.Collections.ObjectModel;
using System.Globalization;

using DbSharp.Framework.Storage;
using DbSharp.Framework.TestEngine;
using DbSharp.Framework.CaseFile;
using DbSharp.Framework.Driver;
using DbSharp.Framework.Common;




namespace DbSharp.Framework.DbObject
{

    /// <summary>
    /// ProcedureRunner was named by SqlStatementRunner
    /// </summary>
    public class StatementRunner
    {
        #region Private memebers

        private ITestDataSource m_DataSource;
        private TransactionController m_TransController;
        private ResultsetSection m_ResultsetSection;
        private IStatementDeclaration m_StatementDeclaration;
        private List<StatementArgument> m_ListArgument = new List<StatementArgument>();
        private List<StatementArgument> m_ListOutRecordsetArg = new List<StatementArgument>();
        private List<StatementArgument> m_ListOutScalarArg = new List<StatementArgument>();
        private List<StatementArgument> m_ListOutResultsetArg = new List<StatementArgument>();
        private StmtExecutionResult m_ExecutionResult = new StmtExecutionResult();
        private IStorageFactory m_StorageFactory;
        private PumpSourceResult m_PumpResultset;
        #endregion

        public bool IsEnumerable(object resultset)
        {
            if (resultset is IEnumerable<XElement>)
                return true;
            else
                return false;
        }


        public StatementRunner(TransactionController transController, IStatementDeclaration statement, ResultsetSection resultsetSection, IStorageFactory storageFactory, PumpSourceResult pumpResultset)
        {
            m_TransController = transController;
            m_ResultsetSection = resultsetSection;
            m_StorageFactory = storageFactory;
            m_PumpResultset = pumpResultset;
            m_StatementDeclaration = statement;
            string providerName = statement.ConnectionProviderName;
            string connectionString = statement.ConnectionString;
            m_DataSource =  RuntimeInfo.GetInstance().TestDataSourceFactory.SelectDbTestPlugin(providerName, connectionString);

            ParseArguments();
        }

        #region Private methods

        private void ParseArguments()
        {
            m_ListArgument.Clear();
            m_ListOutResultsetArg.Clear();
            m_ListOutRecordsetArg.Clear();
            m_ListOutScalarArg.Clear();
            m_ExecutionResult.Reset();

            //Normalize the argument names in order to following the specific database server
            foreach (var argument in m_StatementDeclaration.ListArgument)
            {
                argument.Name = m_DataSource.NormalizeArgumentName(argument.Name);
                m_ListArgument.Add(argument);
            }

            //pick out these output arguments
            foreach (var argument in m_ListArgument)
            {
                if (argument.Direction != ArgumentDirection.Input)
                {
                    m_ListOutResultsetArg.Add(argument);
                    if (m_DataSource.IsRecordsetTypeArgument(argument.TypeStr))
                        m_ListOutRecordsetArg.Add(argument);
                    else
                        m_ListOutScalarArg.Add(argument);
                }
            }
        }


        private bool NeedSaveResult(StatementArgument argument)
        {
            bool needSave = false;
            if (m_ResultsetSection == null)
            {
                needSave = false;
            }
            else if (m_ResultsetSection.OutputSetting.OutputAllOutArguments)
            {
                needSave = true;
            }
            else
            {
                string normalizedOuputArgName = m_ResultsetSection.OutputSetting.OutputArgument;
                normalizedOuputArgName = m_DataSource.NormalizeArgumentName(normalizedOuputArgName);
                if (string.Equals(argument.Name, normalizedOuputArgName, StringComparison.OrdinalIgnoreCase))
                    needSave = true;
            }
            return needSave;
        }


        private void PushScalarExecutionResult(StatementArgument scalarArgument, DbCmdScalarResult scalarResult)
        {
            var executionResult = new ScalarExecutionResult(scalarArgument);

            IScalarStorage valueStorage = null;
            if ((m_StorageFactory != null) && (m_ResultsetSection != null))
                valueStorage = m_StorageFactory.NewScalarStorage(m_ResultsetSection, m_DataSource);
            if ((valueStorage != null) && NeedSaveResult(scalarArgument))
            {
                valueStorage.Initialize();
                valueStorage.Save(scalarResult);
                valueStorage.Terminate();
                executionResult.Storage = valueStorage;
                executionResult.ScalarValue = valueStorage.ScalarText;
                executionResult.ScalarValueXElement = valueStorage.ScalarValueXElement;
                executionResult.OutputFieldNameList = valueStorage.GetOutputFieldNameList();
                executionResult.OutputFieldMetaDataQueue = valueStorage.GetOutputFieldMetaDataQueue();
            }
            m_ExecutionResult.ListScalarExecutionResult.Add(executionResult);
        }


        private void FetchOutScalarValuesInParams(DbCommand cmd)
        {
            for (int i = 0; i < m_ListOutScalarArg.Count; i++)
            {
                StatementArgument outArgument = m_ListOutScalarArg[i];
                string argumentName = outArgument.Name;

                //find outArgument's corresponding DbParameter
                foreach (DbParameter param in cmd.Parameters)
                {
                    if (string.Equals(param.ParameterName, argumentName, StringComparison.OrdinalIgnoreCase))
                    {
                        var scalarValue = param.Value;
                        var scalarValueType = m_DataSource.FieldPersistenceService.GetSpecificDbType(param);
                        var scalar = new DbCmdScalarResult(scalarValue, scalarValueType);

                        PushScalarExecutionResult(outArgument, scalar);
                        break;
                    }
                }
            }
        }

        /// <summary>
        /// Oracle style stored procedure, SP uses named arguments to hold resultsets,
        /// So we can use argument name to retrieve its result data
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="scalarValue"></param>
        private void FetchScalarValues(DbCommand cmd, DbCmdScalarResult scalarResult)
        {
            StatementArgument sqlResultArg = null;
            bool isSqlWithReturnValue = StatementDeclarationExt.IsSqlStmtWithReturnValue(m_StatementDeclaration, out sqlResultArg);

            if (isSqlWithReturnValue)
            {
                #region it indicates this statement simple SQL
                if (this.m_DataSource.IsRecordsetTypeArgument(sqlResultArg.TypeStr) == false)
                    //it indicates this statement is select SQL statement
                    PushScalarExecutionResult(sqlResultArg, scalarResult);
                #endregion
            }
            else
            {
                //it indicates this statement is stored procedure/function
                FetchOutScalarValuesInParams(cmd);
            }
        }


        /// <summary>
        /// Find param's corresponding StatementArgument in m_ListOutRecordsetArg
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        private StatementArgument FindRecordsetArgument(DbParameter param)
        {
            if (param == null)
                return null;
            else
            {
                return m_ListOutRecordsetArg.First(x =>
                    string.Equals(x.Name, param.ParameterName, StringComparison.OrdinalIgnoreCase));
            }
        }


        /// <summary>
        /// Find 1st recordset parameter in cmd.Parameters
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="startParamIndex"></param>
        /// <returns></returns>
        private DbParameter FindFirstRecordsetParameter(DbCommand cmd, int startParamIndex)
        {
            DbParameter param = null;
            for (int i = startParamIndex; i < cmd.Parameters.Count; i++)
            {
                if (m_DataSource.IsRecordsetTypeParameter(cmd.Parameters[i]))
                {
                    param = cmd.Parameters[i];
                    break;
                }
            }
            return param;
        }


        /// <summary>
        /// Sql server style stored procedure, SP uses anonymous arguments to hold resultsets
        /// So we only can use argument position to retrieve its result data
        /// 
        /// In order to get all scalar style resultset, call DbCommandExecuteType.ExecuteReader rather than DbCommandExecuteType.ExecuteScalar
        /// http://www.cnblogs.com/aspxphpjsprb/archive/2008/01/25/1052312.html
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dataReader"></param>
        private void FetchDataReaderByArgPosition(DbCommand cmd, DbDataReader dataReader)
        {
            if ((cmd == null) || (dataReader == null))
                return;

            //Step 1: Fetch Resultset from dataReader
            object scalarValue = null;
            int scalarValueType = -1;
            DbCmdScalarResult scalarResult;
            StatementArgument outputArgument;
            int index = 0;
            do
            {
                if (index >= m_ListOutResultsetArg.Count)
                    return;
                outputArgument = m_ListOutResultsetArg[index];
                if (m_DataSource.IsRecordsetTypeArgument(outputArgument.TypeStr) == false)
                {
                    #region Get scalar value

                    string errorMsgFmt1 = "The argument '{0}' of statement '{1}' is not scalar argument.";
                    string errorMsg1 = string.Format(errorMsgFmt1, outputArgument.Name, m_StatementDeclaration.Alias);

                    string errorMsgFmt2 = "The statement '{0}' has no resultset.";
                    string errorMsg2 = string.Format(errorMsgFmt2, m_StatementDeclaration.Alias);
                    if (dataReader.Read())
                    {
                        try
                        {
                            scalarValue = dataReader[0];
                            scalarValueType = m_DataSource.FieldPersistenceService.GetSpecificDbType(dataReader, 0);
                            scalarResult = new DbCmdScalarResult(scalarValue, scalarValueType);
                        }
                        catch
                        { throw new DbSharpException(errorMsg1); }
                    }
                    else
                    {
                        throw new DbSharpException(errorMsg2);
                    }

                    #endregion
                    PushScalarExecutionResult(outputArgument, scalarResult);
                }
                else
                {
                    PushRecordsetExecutionResult(outputArgument, dataReader);
                }
                index++;
            } while (dataReader.NextResult());


            //Step 2: Fetch Resultset in Cmd.Parameters
            FetchOutScalarValuesInParams(cmd);
        }

        private void PushRecordsetExecutionResult(StatementArgument outputArgument, DbDataReader dataReader)
        {
            List<XElement> bagXElmntRcd = null;
            List<string> outputFieldNameList = null;
            List<FieldMetaData> outputFieldMetaDataQueue = null;

            Stopwatch recordsetStopwatch = Stopwatch.StartNew();
            bool needSaveRecordset = NeedSaveResult(outputArgument);
            IRecordsetStorage recordsetStorage = null;
            if (needSaveRecordset)
            {
                if ((m_StorageFactory != null) && (m_ResultsetSection != null))
                    recordsetStorage = m_StorageFactory.NewRcdSetStorage(m_ResultsetSection, m_DataSource);
            }

            if ((recordsetStorage != null) && needSaveRecordset)
                recordsetStorage.Initialize(dataReader);
            Trace.TraceInformation("SQLRun step53:" + DateTime.Now.Ticks);
            while (dataReader.Read())
            {
                if ((recordsetStorage != null) && needSaveRecordset)
                    recordsetStorage.SaveRecord(dataReader);
            }
            Trace.TraceInformation("SQLRun step54:" + DateTime.Now.Ticks);
            if ((recordsetStorage != null) && needSaveRecordset)
            {
                recordsetStorage.Terminate();
                bagXElmntRcd = (List<XElement>)(recordsetStorage.RecordsetContainer);
                outputFieldNameList = recordsetStorage.GetOutputFieldNameList();
                outputFieldMetaDataQueue = recordsetStorage.GetOutputFieldMetaDataQueue(); 
                recordsetStopwatch.Stop();

                var recordsetRunResult = new RecordsetExecutionResult(outputArgument);
                recordsetRunResult.FetchDataMilliseconds = recordsetStopwatch.ElapsedMilliseconds;
                recordsetRunResult.Storage = recordsetStorage;
                recordsetRunResult.XElmtRecords = bagXElmntRcd;
                recordsetRunResult.OutputFieldNameList = outputFieldNameList;
                recordsetRunResult.OutputFieldMetaDataQueue = outputFieldMetaDataQueue;
                m_ExecutionResult.ListRecordsetExecutionResult.Add(recordsetRunResult);
            }
        }



        /// <summary>
        /// Oracle style stored procedure, SP uses named arguments to hold resultsets, 
        /// So we can use argument name to retrieve its result data
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dataReader"></param>
        private void FetchDataReaderByArgName(DbCommand cmd, DbDataReader dataReader)
        {
            if ((cmd == null) || (dataReader == null))
                return;
            Trace.TraceInformation("SQLRun step51:" + DateTime.Now.Ticks);

            int startParamIndex = 0;
            StatementArgument outputArgument = null;

            #region loop fetch dataReader
            do
            {
                #region find corresponding OutputArgument for every dataReader

                StatementArgument sqlResultArg = null;
                bool isSqlWithReturnValue = StatementDeclarationExt.IsSqlStmtWithReturnValue(m_StatementDeclaration, out sqlResultArg);
                if (isSqlWithReturnValue)
                {
                    if (this.m_DataSource.IsRecordsetTypeArgument(sqlResultArg.TypeStr))
                        //it indicates this statement is select SQL statement
                        outputArgument = sqlResultArg;
                }
                else
                {
                    DbParameter recordsetParam = FindFirstRecordsetParameter(cmd, startParamIndex);
                    if (recordsetParam == null)
                        break;
                    startParamIndex = cmd.Parameters.IndexOf(recordsetParam) + 1;
                    outputArgument = FindRecordsetArgument(recordsetParam);
                }
                #endregion

                Trace.TraceInformation("SQLRun step52:" + DateTime.Now.Ticks);
                if (outputArgument == null)
                    break;

                PushRecordsetExecutionResult(outputArgument, dataReader);

                Trace.TraceInformation("SQLRun step55:" + DateTime.Now.Ticks);

            } while (dataReader.NextResult());

            #endregion
        }


        private StatementArgument FindArgumentValue(string parameterName)
        {
            StatementArgument result = null;
            //foreach (var arg in m_StatementDeclaration.ListArgument)
            foreach (var arg in m_ListArgument)
            {
                if (string.Equals(arg.Name, parameterName, StringComparison.OrdinalIgnoreCase))
                {
                    result = arg;
                    break;
                }
            }
            return result;
        }



        /// <summary>
        /// Replace all client side parameters in statement commandText
        /// </summary>
        private void ReplaceClientSideParams(DbCommand cmd)
        {
            string cmdTextWithoutClientSideParams = m_StatementDeclaration.CommandText;

            //TODO: Replace client side parameters in statement
            //...
            m_DataSource.UpdateCommandText(cmd, cmdTextWithoutClientSideParams);
        }

        /// <summary>
        /// Update parameters' value of cmd before cmd execution
        /// </summary>
        /// <param name="cmd"></param>
        private void UpdateCmdParamValues(DbCommand cmd)
        {
            foreach (DbParameter parameter in cmd.Parameters)
            {
                parameter.Value = null;
                //init parameters' value
                if ((parameter.Direction == ParameterDirection.Input)
                    || (parameter.Direction == ParameterDirection.InputOutput))
                {
                    var arg = FindArgumentValue(parameter.ParameterName);
                    m_DataSource.AssignParameterValue(parameter, arg.Value, arg.DataTimeFormat);
                }
            }
        }



        private void InternalExecuteCmd(DbCommand cmd, ITestCaseStatusNotify statusNotifier)
        {
            #region Update status
            TestCaseStatusNotifyEventArgs statusArgs = null;
            if (statusNotifier != null)
            {
                statusArgs = new TestCaseStatusNotifyEventArgs();
                statusArgs.TestPhase = CaseRunPhase.Execution;
                statusArgs.ErrorMessage = string.Empty;
                statusArgs.TestRunner = statusNotifier.TestRunner;
                statusNotifier.UpdateStatus(statusArgs);
            }

            #endregion

            ReplaceClientSideParams(cmd);
            UpdateCmdParamValues(cmd);

            Trace.TraceInformation("SQLRun step1:" + DateTime.Now.Ticks);
            Stopwatch stopwatch = Stopwatch.StartNew();
            #region execute the procedure
            DbDataReader dataReader = null;
            DbCommandExecuteType executeType;
            executeType = m_StatementDeclaration.GetCommandExecuteType(m_DataSource);
            if (m_DataSource.HoldResultsetMustWithNamedArgInSP == false)
            {
                //for SQL Server style DB, if even there is only one resultset, we always call ExecuteReader() rather than ExecuteScalar() ;
                if (executeType == DbCommandExecuteType.ExecuteScalar)
                    executeType = DbCommandExecuteType.ExecuteReader;
            }
            DbCmdScalarResult scalarValue = null;
            Trace.TraceInformation("SQLRun step2:" + DateTime.Now.Ticks);
            switch (executeType)
            {
                case DbCommandExecuteType.ExecuteScalar:
                    //in order to get value type, do not use cmd.ExecuteScalar(), use DbCmdScalarExecution.ExecuteScalar() instead
                    DbCmdScalarExecution exec = new DbCmdScalarExecution();
                    scalarValue = exec.ExecuteScalar(cmd, m_DataSource);
                    break;
                case DbCommandExecuteType.ExecuteNoQuery:
                    cmd.ExecuteNonQuery();
                    break;
                case DbCommandExecuteType.ExecuteReader:
                    dataReader = cmd.ExecuteReader();
                    break;
                default:
                    cmd.ExecuteNonQuery();
                    break;
            }
            #endregion
            stopwatch.Stop();
            m_ExecutionResult.ExecuteMilliseconds = stopwatch.ElapsedMilliseconds;
            Trace.TraceInformation("SQLRun step3:" + DateTime.Now.Ticks);

            #region Update status

            if (statusNotifier != null)
            {
                statusArgs.TestPhase = CaseRunPhase.FetchData;
                statusArgs.ErrorMessage = string.Empty;
                statusNotifier.UpdateStatus(statusArgs);
            }

            #endregion

            stopwatch.Reset();
            stopwatch.Start();

            #region Fetch data
            try
            {
                Trace.TraceInformation("SQLRun step4:" + DateTime.Now.Ticks);
                m_ExecutionResult.ListScalarExecutionResult.Clear();
                m_ExecutionResult.ListRecordsetExecutionResult.Clear();
                if (m_DataSource.HoldResultsetMustWithNamedArgInSP)
                {
                    FetchScalarValues(cmd, scalarValue);
                    if (dataReader != null)
                        FetchDataReaderByArgName(cmd, dataReader);
                }
                else
                {
                    FetchDataReaderByArgPosition(cmd, dataReader);
                }
                Trace.TraceInformation("SQLRun step6:" + DateTime.Now.Ticks);
            }
            finally
            {
                if (dataReader != null)
                    dataReader.Close();
            }
            m_ExecutionResult.FetchDataMilliseconds = stopwatch.ElapsedMilliseconds;
            #endregion
            stopwatch.Stop();
        }

        private void InternalExecute(DbConnection connection, DbTransaction transaction, ITestCaseStatusNotify statusNotifier)
        {
            Trace.TraceInformation("SQLRun step1:" + DateTime.Now.Ticks);
            DbCommand cmd = m_DataSource.PrepareCommand(m_StatementDeclaration);
            cmd.Connection = connection;
            cmd.Transaction = transaction;

            //case 1: no pump source
            if (m_PumpResultset == null)
            {
                //not need to update argument values in this case
                //execute statement
                InternalExecuteCmd(cmd, statusNotifier);
            }

            //case 2: pump source is scalar value
            else if (m_PumpResultset.ResultsetIsScalar)
            {
                //update argument values
                //foreach (var arg in m_StatementDeclaration.ListArgument)
                foreach (var arg in m_ListArgument)
                {
                    if (arg.ValueNeedLookUp)
                        if (string.Equals(m_PumpResultset.ArgNameIfIsScalar, arg.ValueFieldInLookupStatement, StringComparison.OrdinalIgnoreCase))
                            arg.Value = m_PumpResultset.ResultsetData.ToString();
                }

                //execute statement
                InternalExecuteCmd(cmd, statusNotifier);
            }

            //case 3: pump source is record set
            else
            {
                var pumpRecordset = (IEnumerable<XElement>)m_PumpResultset.ResultsetData;
                foreach (XElement row in pumpRecordset)
                {
                    //update argument values
                    /*
                    <row><FIELDNAME1>value1</FIELDNAME1><FIELDNAME2>value2</FIELDNAME2></row>
                    <row><FIELDNAME1>value1</FIELDNAME1><FIELDNAME2>value2</FIELDNAME2></row>
                    */

                    //foreach (var arg in m_StatementDeclaration.ListArgument)
                    foreach (var arg in m_ListArgument)
                    {
                        if (arg.ValueNeedLookUp)
                        {
                            var element = row.Element(arg.ValueFieldInLookupStatement.ToUpper());
                            if (element != null)
                                arg.Value = element.Value;
                        }
                    }

                    //execute statement
                    InternalExecuteCmd(cmd, statusNotifier);
                }
            }
        }


        #endregion


        #region Public properties/methods

        public ITestDataSource TestDataSource
        {
            get { return m_DataSource; }
        }


        public void Run(ITestCaseStatusNotify statusNotifier)
        {
            m_ExecutionResult.Reset();
            try
            {
                DbConnection dbConn = m_TransController.OpenDbConnection(m_StatementDeclaration.ConnectionString, m_DataSource);
                DbTransaction transaction = m_TransController.GetTransaction(m_StatementDeclaration.ConnectionString);
                InternalExecute(dbConn, transaction, statusNotifier);
            }
            catch (Exception ex)
            {
                string errorFmt = "Message: {0}; Source: {1}";
                string exceptionSource = ex.Source;
                if (string.IsNullOrEmpty(exceptionSource))
                    exceptionSource = "Unknown";

                m_ExecutionResult.ErrorMessage = string.Format(errorFmt, ex.Message, exceptionSource);
                throw;
            }
        }


        public StmtExecutionResult ExecutionResult
        {
            get { return m_ExecutionResult; }
        }


        /// <summary>
        /// Get the resultset for assertion
        /// </summary>
        /// <param name="resultset">assertion resultset</param>
        /// <param name="isRecordset">is resultset or is scalarValue</param>
        /// <param name="indexInList">
        /// index of ListRecordsetExecutionResult for isRecordset=true
        /// or, index of ListScalarExecutionResult for isRecordset=false
        /// </param>
        /// <returns>true when found, else return false</returns>
        public bool TryGetAssertionResultset(out object resultset, out List<string> outputFieldNameList, out List<FieldMetaData> outputFieldMetaDataQueue, out bool isRecordset, out int indexInList)
        {
            bool result = false;
            isRecordset = false;
            indexInList = -1;
            resultset = null;
            outputFieldNameList = null;
            outputFieldMetaDataQueue = null;

            if (m_ExecutionResult.ListRecordsetExecutionResult.Count > 0)
            {
                //m_ExecutionResult.ListRecordsetExecutionResult.Count at most is ONE. this is rule.
                resultset = m_ExecutionResult.ListRecordsetExecutionResult[0].XElmtRecords;
                outputFieldNameList = m_ExecutionResult.ListRecordsetExecutionResult[0].OutputFieldNameList;
                outputFieldMetaDataQueue = m_ExecutionResult.ListRecordsetExecutionResult[0].OutputFieldMetaDataQueue;
                isRecordset = true;
                indexInList = 0;
                result = true;
            }
            else
            {
                for (int i = 0; i < m_ExecutionResult.ListScalarExecutionResult.Count; i++)
                {
                    //only verify those scalar arguments that have storage
                    if (m_ExecutionResult.ListScalarExecutionResult[i].Storage != null)
                    {
                        resultset = m_ExecutionResult.ListScalarExecutionResult[i].ScalarValue;
                        outputFieldNameList = m_ExecutionResult.ListScalarExecutionResult[i].OutputFieldNameList;
                        outputFieldMetaDataQueue = m_ExecutionResult.ListScalarExecutionResult[i].OutputFieldMetaDataQueue;
                        isRecordset = false;
                        indexInList = i;
                        result = true;
                    }
                }
            }
            return result;
        }


        public long GetExportRecordCount()
        {
            OutputSettingAttribute settingAttr;
            Dictionary<string, string> listFieldOutputName;
            Dictionary<string, string> listFieldNullPlaceHolder;

            StorageFactoryHelper.GetResultsetOutputSetting(m_ResultsetSection, out settingAttr, out listFieldOutputName, out listFieldNullPlaceHolder);
            return settingAttr.ExportRecordCount;
        }

        #endregion


    }
}
